/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package com.tesam.gedeon.dao.impl;
 
import com.tesam.gedeon.dao.CountryProviderDao;
import com.tesam.gedeon.entity.Country;
import com.tesam.gedeon.entity.CountryProvider;
import com.tesam.gedeon.entity.Provider;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;
import javax.sql.DataSource;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.jdbc.core.simple.SimpleJdbcDaoSupport;
import org.springframework.stereotype.Repository;


/**
 *
 * @author Sistema
 */
@Repository
public class CountryProviderDaoImpl extends SimpleJdbcDaoSupport implements CountryProviderDao{
    
    @Autowired
    public CountryProviderDaoImpl(DataSource dataSource) {
        setDataSource(dataSource);
    }

    @Override
    public int getCountAll() {
        return getJdbcTemplate().queryForInt("select count(*) as all from countryprovider cp " +
                                             "inner join provider pr on cp.providerid = pr.id  " +
                                             "inner join country co on cp.countrybillingcode = co.code " +
                                             "where cp.deleted = 0 ");
    }

    @Override
    public int getCountBySearch(CountryProvider countryProvider) {
        return getJdbcTemplate().queryForInt("select count(*) as all  from countryprovider cp " +
                            "inner join provider pr on cp.providerid = pr.id  " +
                            "inner join country co on cp.countrybillingcode = co.code " +
                            "where cp.deleted = 0 and upper(cp.countryprovidercode) like '%"+countryProvider.getCountryProviderCode().toUpperCase()+"%' ");
    }

    @Override
    public List<CountryProvider> getCountryProviderByLimit(String dataToSort, String directionOfOrder, int startLimit, int endLimit) {
        List<CountryProvider> result = new ArrayList();
            try{
               String sql = "select cp.* , pr.name as providerName , pr.code as providerCode , "+
                            "co.id as countryId , co.longcode, co.code as codeCountry from countryprovider cp " +
                            "inner join provider pr on cp.providerid = pr.id  " +
                            "inner join country co on cp.countrybillingcode = co.code " +
                            "where cp.deleted = 0 "  +
                        "order by "+dataToSort+" "+directionOfOrder+" limit "+endLimit+" offset "+startLimit;
                    
 
                List<Map<String, Object>> rows = getJdbcTemplate().queryForList(sql);
                for (Map row : rows) {
		CountryProvider countryProvider = new CountryProvider();
		countryProvider.setId((Integer)(row.get("id"))); 
                countryProvider.setCountryProviderCode((String)row.get("countryprovidercode"));
                
                Country country = new Country();
                country.setName((String)row.get("name"));
                country.setId((Integer)row.get("countryId")); 
                countryProvider.setCountry(country);
                
                Provider provider = new Provider();
                provider.setId((Integer)row.get("providerid"));
                provider.setName((String)row.get("providerName"));
                provider.setCode((String)row.get("providerCode"));
                countryProvider.setProvider(provider);
                
		result.add(countryProvider);
                }
            }catch(Exception e){
                System.out.println("getCountryProviderByLimit : " + e.getMessage());
            }
        return result ;
    }

    @Override
    public List<CountryProvider> getCountryProviderBySearch(String dataToSort, String directionOfOrder, int startLimit, int endLimit, CountryProvider countryProvider) {
        List<CountryProvider> result = new ArrayList();
            try{
               String sql = "select cp.* , pr.name as providerName , pr.code as providerCode , "+
                            "co.id as countryId , co.longcode , co.code as codeCountry from countryprovider cp " +
                            "inner join provider pr on cp.providerid = pr.id  " +
                            "inner join country co on cp.countrybillingcode = co.code " +
                            "where cp.deleted = 0 and upper(cp.countryprovidercode) like '%"+countryProvider.getCountryProviderCode().toUpperCase()+"%' "  +
                        "order by "+dataToSort+" "+directionOfOrder+" limit "+endLimit+" offset "+startLimit;
                    
 
                List<Map<String, Object>> rows = getJdbcTemplate().queryForList(sql);
                for (Map row : rows) {
		CountryProvider countryProviderE = new CountryProvider();
		countryProviderE.setId((Integer)(row.get("id"))); 
                countryProviderE.setCountryProviderCode((String)row.get("countryprovidercode")); 
                Country country = new Country();
                country.setName((String)row.get("name"));
                country.setId((Integer)row.get("countryId"));  
                
                countryProviderE.setCountry(country);
                
                Provider provider = new Provider();
                provider.setId((Integer)row.get("providerid"));
                provider.setName((String)row.get("providerName"));
                provider.setCode((String)row.get("providerCode"));
                countryProviderE.setProvider(provider);
                
		result.add(countryProviderE);
                }
            }catch(Exception e){
                System.out.println("getCountryProviderByLimit : " + e.getMessage());
            }
        return result ;
    }

    @Override
    public void delete(CountryProvider countryProvider) {
            getJdbcTemplate().update("update countryprovider set deleted = 1 where id = ?" , countryProvider.getId());
    }

    @Override
    public void update(CountryProvider countryProvider) {
        getJdbcTemplate().update("update countryprovider set providerid = ?, name = ? ,"+ 
                                 " countrybillingcode = ? , countryprovidercode = ? where id = ? " ,
                        countryProvider.getProvider().getId() ,countryProvider.getCountry().getName() ,
                        countryProvider.getCountry().getCode() , countryProvider.getCountryProviderCode() , 
                        countryProvider.getId());
    }

    @Override
    public int save(CountryProvider countryProvider) {
        int id = 0;
        
        try{
           
            getJdbcTemplate().update("insert into countryprovider "
                    + "(providerid , name , countrybillingcode , "
                    + "countryprovidercode , deleted ) values "
                    + "( ? , ? , ? , ? , 0)" , countryProvider.getProvider().getId() , 
                    countryProvider.getCountry().getName() , countryProvider.getCountry().getCode() , 
                    countryProvider.getCountryProviderCode());
            
            id = getSimpleJdbcTemplate().queryForInt("SELECT last_value FROM countryprovider_id_seq");
        }catch(Exception e){
            System.out.println("save : "+e.getMessage());
        }
        
        return id ;
    }

    @Override
    public List<CountryProvider> getCountryProviderById(CountryProvider countryProvider) {
        List<CountryProvider> result = new ArrayList();
            try{
                
               // getJdbcTemplate().update("update countryprovider set viewedtime = NOW() where id = "+countryProvider.getId());
                
               String sql = "select cp.* , pr.name as providerName , pr.code as providerCode , "+
                            "co.id as countryId , co.longcode, co.code as codeCountry from countryprovider cp " +
                            "inner join provider pr on cp.providerid = pr.id  " +
                            "inner join country co on cp.countrybillingcode = co.code " +
                            "where cp.deleted = 0 and cp.id = "+countryProvider.getId()   ;
                    
               System.out.println(sql);
               
                List<Map<String, Object>> rows = getJdbcTemplate().queryForList(sql);
                for (Map row : rows) {
		CountryProvider countryProviderE = new CountryProvider();
		countryProviderE.setId((Integer)(row.get("id"))); 
                countryProviderE.setCountryProviderCode((String)row.get("countryprovidercode"));
                
                Country country = new Country();
                country.setName((String)row.get("name"));
                country.setId((Integer)row.get("countryId")); 
                countryProviderE.setCountry(country);
                
                Provider provider = new Provider();
                provider.setId((Integer)row.get("providerid"));
                provider.setName((String)row.get("providerName"));
                provider.setCode((String)row.get("providerCode"));
                countryProviderE.setProvider(provider);
                
		result.add(countryProviderE);
                }
            }catch(Exception e){
                System.out.println("getCountryProviderByLimit : " + e.getMessage());
            }
        return result ;
    }
}
